from sqlalchemy import create_engine
import pandas as pd
import matplotlib.pyplot as plt
import numpy as np
import matplotlib as mpl


def fetch_bwic_data(bond_code):
    """从数据库获取特定债券的交易数据"""
    # 创建数据库连接
    db_url = "mysql+pymysql://root:yourpassword@localhost:3306/salessvc?charset=utf8mb4"
    engine = create_engine(db_url, pool_pre_ping=True)

    try:
        # 查询特定债券的市场价格数据
        query = """
        SELECT 
            b.bid_price AS market_price, 
            b.bid_time AS created_time
        FROM 
            auction_assets a
        JOIN 
            auction_bids b ON a.id = b.auction_id
        WHERE 
            a.bond_code = %s
        ORDER BY 
            b.bid_time
        """
        df = pd.read_sql(query, engine, params=(bond_code,))
        return df
    except Exception as e:
        print(f"数据库查询错误: {e}")
        return pd.DataFrame()
    finally:
        engine.dispose()


def analyze_peaks_troughs(df, bond_code):
    """分析价格峰值和谷值并绘制图表"""
    if df.empty:
        print("没有可用的数据进行分析")
        return

    # 数据预处理
    df['created_time'] = pd.to_datetime(df['created_time'])
    df.sort_values('created_time', inplace=True)
    df.reset_index(drop=True, inplace=True)
    df['created_time_str'] = df['created_time'].astype(str)  # 保持与原始代码兼容

    # 计算峰值和谷值
    data = df['market_price'].values
    double_diff = np.diff(np.sign(np.diff(data)))
    peak_locations = np.where(double_diff == -2)[0] + 1
    double_diff2 = np.diff(np.sign(np.diff(-1 * data)))
    trough_locations = np.where(double_diff2 == -2)[0] + 1

    # 绘制图表
    plt.figure(figsize=(16, 10), dpi=80)

    # 绘制价格曲线
    plt.plot('created_time_str', 'market_price', data=df,
             color='tab:blue', label='Trade Price')

    # 标记峰值和谷值
    plt.scatter(df['created_time_str'].iloc[peak_locations],
                df['market_price'].iloc[peak_locations],
                marker=mpl.markers.CARETUPBASE,
                color='tab:green',
                s=100,
                label='Peaks')

    plt.scatter(df['created_time_str'].iloc[trough_locations],
                df['market_price'].iloc[trough_locations],
                marker=mpl.markers.CARETDOWNBASE,
                color='tab:red',
                s=100,
                label='Troughs')

    # 标注部分峰值和谷值
    for t, p in zip(trough_locations[1::5], peak_locations[::5]):
        plt.text(df['created_time_str'].iloc[p],
                 df['market_price'].iloc[p] + 0.5,
                 df['created_time_str'].iloc[p][-8:-3],  # 显示时间部分 HH:MM
                 horizontalalignment='center',
                 color='darkgreen')
        plt.text(df['created_time_str'].iloc[t],
                 df['market_price'].iloc[t] - 0.5,
                 df['created_time_str'].iloc[t][-8:-3],
                 horizontalalignment='center',
                 color='darkred')

    # 设置图表标题和标签
    plt.title(f"Peak and Troughs of Trade Price - {bond_code}", fontsize=22)
    plt.xlabel('Time', fontsize=12)
    plt.ylabel('Price', fontsize=12)

    # 设置x轴刻度
    xtick_location = df.index.tolist()[::max(1, len(df) // 10)]  # 自动调整显示约10个标签
    xtick_labels = [x[-8:-3] for x in df['created_time_str'].tolist()[::max(1, len(df) // 10)]]
    plt.xticks(ticks=xtick_location,
               labels=xtick_labels,
               rotation=90,
               fontsize=12,
               alpha=.7)

    # 设置y轴
    plt.yticks(fontsize=12, alpha=.7)

    # 美化边框和网格
    plt.gca().spines["top"].set_alpha(.0)
    plt.gca().spines["bottom"].set_alpha(.3)
    plt.gca().spines["right"].set_alpha(.0)
    plt.gca().spines["left"].set_alpha(.3)
    plt.grid(axis='y', alpha=.3)
    plt.legend(loc='upper left')

    plt.tight_layout()
    plt.show()


# 主程序
if __name__ == "__main__":
    # 要分析的债券代码
    bond_code = "37DS1526"  # 替换为您要分析的实际债券代码

    # 从数据库获取数据
    bond_data = fetch_bwic_data(bond_code)

    if not bond_data.empty:
        # 分析并绘制图表
        analyze_peaks_troughs(bond_data, bond_code)

        # 打印债券基本信息
        print("\n债券基本信息:")
        print(f"数据记录数: {len(bond_data)}")
        print(f"时间范围: {bond_data['created_time'].iloc[0]} 至 {bond_data['created_time'].iloc[-1]}")
        print(f"价格范围: {bond_data['market_price'].min():.2f} - {bond_data['market_price'].max():.2f}")
    else:
        print(f"未找到债券 {bond_code} 的数据")